package com.vibeplayer.app.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.vibeplayer.app.model.Playlist;
import com.vibeplayer.app.model.Song;

import java.util.ArrayList;
import java.util.List;

/**
 * 数据库帮助类，管理应用的本地数据库
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "vibeplayer.db";
    private static final int DATABASE_VERSION = 1;
    
    // 表名
    private static final String TABLE_PLAYLISTS = "playlists";
    private static final String TABLE_PLAYLIST_SONGS = "playlist_songs";
    private static final String TABLE_RECENTLY_PLAYED = "recently_played";
    
    // 播放列表表的列名
    private static final String COLUMN_PLAYLIST_ID = "id";
    private static final String COLUMN_PLAYLIST_NAME = "name";
    private static final String COLUMN_PLAYLIST_DATE_CREATED = "date_created";
    
    // 播放列表歌曲表的列名
    private static final String COLUMN_PLAYLIST_SONG_ID = "id";
    private static final String COLUMN_PLAYLIST_SONG_PLAYLIST_ID = "playlist_id";
    private static final String COLUMN_PLAYLIST_SONG_SONG_ID = "song_id";
    private static final String COLUMN_PLAYLIST_SONG_POSITION = "position";
    
    // 最近播放表的列名
    private static final String COLUMN_RECENT_ID = "id";
    private static final String COLUMN_RECENT_SONG_ID = "song_id";
    private static final String COLUMN_RECENT_TIME_PLAYED = "time_played";
    
    private static DatabaseHelper instance;
    
    public static synchronized DatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseHelper(context.getApplicationContext());
        }
        return instance;
    }
    
    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建播放列表表
        String createPlaylistsTable = "CREATE TABLE " + TABLE_PLAYLISTS + " (" +
                COLUMN_PLAYLIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_PLAYLIST_NAME + " TEXT NOT NULL, " +
                COLUMN_PLAYLIST_DATE_CREATED + " INTEGER NOT NULL)";
        db.execSQL(createPlaylistsTable);
        
        // 创建播放列表歌曲表
        String createPlaylistSongsTable = "CREATE TABLE " + TABLE_PLAYLIST_SONGS + " (" +
                COLUMN_PLAYLIST_SONG_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " INTEGER NOT NULL, " +
                COLUMN_PLAYLIST_SONG_SONG_ID + " INTEGER NOT NULL, " +
                COLUMN_PLAYLIST_SONG_POSITION + " INTEGER NOT NULL, " +
                "FOREIGN KEY (" + COLUMN_PLAYLIST_SONG_PLAYLIST_ID + ") REFERENCES " +
                TABLE_PLAYLISTS + "(" + COLUMN_PLAYLIST_ID + ") ON DELETE CASCADE)";
        db.execSQL(createPlaylistSongsTable);
        
        // 创建最近播放表
        String createRecentlyPlayedTable = "CREATE TABLE " + TABLE_RECENTLY_PLAYED + " (" +
                COLUMN_RECENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_RECENT_SONG_ID + " INTEGER NOT NULL, " +
                COLUMN_RECENT_TIME_PLAYED + " INTEGER NOT NULL)";
        db.execSQL(createRecentlyPlayedTable);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库时删除旧表并重新创建
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST_SONGS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLISTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECENTLY_PLAYED);
        onCreate(db);
    }
    
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }
    
    /**
     * 创建新的播放列表
     * @param name 播放列表名称
     * @return 新创建的播放列表ID
     */
    public long createPlaylist(String name) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PLAYLIST_NAME, name);
        values.put(COLUMN_PLAYLIST_DATE_CREATED, System.currentTimeMillis());
        
        return db.insert(TABLE_PLAYLISTS, null, values);
    }
    
    /**
     * 删除播放列表
     * @param playlistId 播放列表ID
     * @return 操作是否成功
     */
    public boolean deletePlaylist(long playlistId) {
        SQLiteDatabase db = getWritableDatabase();
        return db.delete(TABLE_PLAYLISTS, COLUMN_PLAYLIST_ID + " = ?", 
                new String[]{String.valueOf(playlistId)}) > 0;
    }
    
    /**
     * 获取所有播放列表
     * @return 播放列表列表
     */
    public List<Playlist> getAllPlaylists() {
        List<Playlist> playlists = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT * FROM " + TABLE_PLAYLISTS + " ORDER BY " + COLUMN_PLAYLIST_NAME;
        Cursor cursor = db.rawQuery(query, null);
        
        if (cursor.moveToFirst()) {
            do {
                long id = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_ID));
                String name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_NAME));
                long dateCreated = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_DATE_CREATED));
                
                Playlist playlist = new Playlist(id, name, dateCreated);
                playlists.add(playlist);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return playlists;
    }
    
    /**
     * 向播放列表添加歌曲
     * @param playlistId 播放列表ID
     * @param songId 歌曲ID
     * @return 操作是否成功
     */
    public boolean addSongToPlaylist(long playlistId, long songId) {
        SQLiteDatabase db = getWritableDatabase();
        
        // 检查歌曲是否已经在播放列表中
        String checkQuery = "SELECT COUNT(*) FROM " + TABLE_PLAYLIST_SONGS +
                " WHERE " + COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " = ? AND " +
                COLUMN_PLAYLIST_SONG_SONG_ID + " = ?";
        Cursor cursor = db.rawQuery(checkQuery, 
                new String[]{String.valueOf(playlistId), String.valueOf(songId)});
        
        if (cursor.moveToFirst() && cursor.getInt(0) > 0) {
            cursor.close();
            return false; // 歌曲已存在于播放列表中
        }
        cursor.close();
        
        // 获取播放列表中的下一个位置
        String positionQuery = "SELECT MAX(" + COLUMN_PLAYLIST_SONG_POSITION + ") FROM " +
                TABLE_PLAYLIST_SONGS + " WHERE " + COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " = ?";
        cursor = db.rawQuery(positionQuery, new String[]{String.valueOf(playlistId)});
        
        int position = 0;
        if (cursor.moveToFirst() && !cursor.isNull(0)) {
            position = cursor.getInt(0) + 1;
        }
        cursor.close();
        
        // 添加歌曲到播放列表
        ContentValues values = new ContentValues();
        values.put(COLUMN_PLAYLIST_SONG_PLAYLIST_ID, playlistId);
        values.put(COLUMN_PLAYLIST_SONG_SONG_ID, songId);
        values.put(COLUMN_PLAYLIST_SONG_POSITION, position);
        
        return db.insert(TABLE_PLAYLIST_SONGS, null, values) != -1;
    }
    
    /**
     * 从播放列表中移除歌曲
     * @param playlistId 播放列表ID
     * @param songId 歌曲ID
     * @return 操作是否成功
     */
    public boolean removeSongFromPlaylist(long playlistId, long songId) {
        SQLiteDatabase db = getWritableDatabase();
        return db.delete(TABLE_PLAYLIST_SONGS,
                COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " = ? AND " + COLUMN_PLAYLIST_SONG_SONG_ID + " = ?",
                new String[]{String.valueOf(playlistId), String.valueOf(songId)}) > 0;
    }
    
    /**
     * 获取播放列表中的所有歌曲ID
     * @param playlistId 播放列表ID
     * @return 歌曲ID列表
     */
    public List<Long> getSongIdsInPlaylist(long playlistId) {
        List<Long> songIds = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT " + COLUMN_PLAYLIST_SONG_SONG_ID + " FROM " + TABLE_PLAYLIST_SONGS +
                " WHERE " + COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " = ? ORDER BY " + COLUMN_PLAYLIST_SONG_POSITION;
        
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(playlistId)});
        
        if (cursor.moveToFirst()) {
            do {
                songIds.add(cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_SONG_SONG_ID)));
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return songIds;
    }
    
    /**
     * 添加歌曲到最近播放列表
     * @param songId 歌曲ID
     */
    public void addToRecentlyPlayed(long songId) {
        SQLiteDatabase db = getWritableDatabase();
        
        // 删除之前的记录（如果存在）
        db.delete(TABLE_RECENTLY_PLAYED, COLUMN_RECENT_SONG_ID + " = ?", 
                new String[]{String.valueOf(songId)});
        
        // 添加新记录
        ContentValues values = new ContentValues();
        values.put(COLUMN_RECENT_SONG_ID, songId);
        values.put(COLUMN_RECENT_TIME_PLAYED, System.currentTimeMillis());
        
        db.insert(TABLE_RECENTLY_PLAYED, null, values);
        
        // 保持最近播放列表不超过50首歌
        String limitQuery = "DELETE FROM " + TABLE_RECENTLY_PLAYED +
                " WHERE " + COLUMN_RECENT_ID + " NOT IN (SELECT " + COLUMN_RECENT_ID +
                " FROM " + TABLE_RECENTLY_PLAYED +
                " ORDER BY " + COLUMN_RECENT_TIME_PLAYED + " DESC LIMIT 50)";
        
        db.execSQL(limitQuery);
    }
    
    /**
     * 获取最近播放的歌曲ID列表
     * @param limit 限制数量
     * @return 歌曲ID列表
     */
    public List<Long> getRecentlyPlayedSongIds(int limit) {
        List<Long> songIds = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT " + COLUMN_RECENT_SONG_ID + " FROM " + TABLE_RECENTLY_PLAYED +
                " ORDER BY " + COLUMN_RECENT_TIME_PLAYED + " DESC LIMIT " + limit;
        
        Cursor cursor = db.rawQuery(query, null);
        
        if (cursor.moveToFirst()) {
            do {
                songIds.add(cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_RECENT_SONG_ID)));
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return songIds;
    }
    
    /**
     * 获取指定ID的播放列表
     * @param playlistId 播放列表ID
     * @return 播放列表对象，如果不存在则返回null
     */
    public Playlist getPlaylist(long playlistId) {
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT * FROM " + TABLE_PLAYLISTS + 
                      " WHERE " + COLUMN_PLAYLIST_ID + " = ?";
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(playlistId)});
        
        Playlist playlist = null;
        if (cursor.moveToFirst()) {
            String name = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_NAME));
            long dateCreated = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_DATE_CREATED));
            playlist = new Playlist(playlistId, name, dateCreated);
        }
        
        cursor.close();
        return playlist;
    }

    /**
     * 获取播放列表中的所有歌曲ID，按位置排序
     * @param playlistId 播放列表ID
     * @return 歌曲ID列表
     */
    public List<Long> getPlaylistSongIds(long playlistId) {
        List<Long> songIds = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT " + COLUMN_PLAYLIST_SONG_SONG_ID + 
                      " FROM " + TABLE_PLAYLIST_SONGS +
                      " WHERE " + COLUMN_PLAYLIST_SONG_PLAYLIST_ID + " = ?" +
                      " ORDER BY " + COLUMN_PLAYLIST_SONG_POSITION;
        
        Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(playlistId)});
        
        if (cursor.moveToFirst()) {
            do {
                long songId = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_PLAYLIST_SONG_SONG_ID));
                songIds.add(songId);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return songIds;
    }
} 